# -*- coding: utf-8 -*-
# @Time : 2022/3/21 14:55
# @FileName: DBTool.py
# @Mail: lizhigen1996@aliyun.com

__author__ = 'Zhigen.li'

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, UniqueConstraint, Index, Boolean
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, close_all_sessions
from datetime import datetime
import pymysql
import socket
import uuid
from datetime import datetime

sqlUrl = 'mysql+pymysql://root:lizhigen1996@47.99.66.66/fund?charset=utf8mb4'

Base = declarative_base()

# 创建单表
class UserInfo(Base):
    __tablename__ = 'userinfo'
    id = Column(Integer, primary_key=True)
    name = Column(String(64), nullable=False)
    password = Column(String(12), nullable=False)
    robotCode = Column(String(32), nullable=False)
    fundCount = Column(Integer, default=3)
    ip = Column(String(32), nullable=False)
    allowLogin = Column(Boolean, default=True)
    lastlandingtime = Column(String(32), default='')

class Setting(Base):
    __tablename__ = 'setting'
    id = Column(Integer, primary_key=True)
    skey = Column(String(32), nullable=False)
    svalue = Column(String(512), nullable=False)

class Update(Base):
    __tablename__ = 'update'
    id = Column(Integer, primary_key=True)
    version = Column(String(32), nullable=False)
    url = Column(String(128), nullable=False)
    forcedUpdating = Column(Boolean, default=False)

class Suggest(Base):
    __tablename__ = 'suggest'
    id = Column(Integer, primary_key=True)
    username = Column(String(64), nullable=False)
    stime = Column(String(32), default=datetime.now().strftime('%Y/%m/%d %H:%M:%S'))
    text = Column(String(2048), nullable=False)
    readed = Column(Boolean, default=False)

class ErrorMsg(Base):
    __tablename__ = 'errormsg'
    id = Column(Integer, primary_key=True)
    username = Column(String(64), nullable=False)
    stime = Column(String(32), default=datetime.now().strftime('%Y/%m/%d %H:%M:%S'))
    text = Column(String(2048), nullable=False)

def getIP():
    st = socket.socket(socket.AF_INET, socket.SOCK_DGRAM)
    try:
        st.connect(('10.255.255.255', 1))
        IP = st.getsockname()[0]
    except Exception:
        IP = '127.0.0.1'
    finally:
        st.close()
    return IP

class DBSession():
    def __init__(self):
        self.engine = create_engine(sqlUrl, max_overflow=15)
        Session = sessionmaker(bind=self.engine)  # 创建session
        self.session = Session()
        self.init_db()

    def init_db(self):
        Base.metadata.create_all(self.engine)

    def get_mac_address(self):
        mac = uuid.UUID(int=uuid.getnode()).hex[-12:]
        return ":".join([mac[e:e + 2] for e in range(0, 11, 2)])

    def checkUserInfo(self, name, pw):
        user = self.session.query(UserInfo).filter_by(name=name).first()
        if user:
            return user.name, user.password, user.robotCode, user.fundCount, user.ip, user.allowLogin
        else:
            if self.checkRobotCode():
                user = UserInfo()
                user.name = name[:8]
                user.password = pw[:12]
                user.robotCode = self.get_mac_address()
                user.ip = getIP()

                setting_dict = self.getSetting()
                count = int(setting_dict.get('registerMaxCount', 3))
                user.fundCount = count

                self.session.add(user)
                self.session.commit()
                return user.name, user.password, user.robotCode, user.fundCount, user.ip, user.allowLogin
        return name, None, self.get_mac_address(), 0, getIP(), False

    def setErrorMsg(self, name, msg):
        try:
            errorMsg = ErrorMsg()
            errorMsg.username = name
            errorMsg.text = msg
            self.session.add(errorMsg)
            self.session.commit()
        except:
            pass
        finally:
            pass

    def loginWithNoUI(self):
        name = self.get_mac_address()
        pw = '12345678'

        user = self.session.query(UserInfo).filter_by(name=name).first()
        if user:
            user.lastlandingtime = datetime.now().strftime('%Y/%m/%d %H:%M:%S')
            self.session.commit()
            return user.name, user.password, user.robotCode, user.fundCount, user.ip, user.allowLogin
        else:
            if self.checkRobotCode():
                user = UserInfo()
                user.name = name
                user.password = pw
                user.robotCode = self.get_mac_address()
                user.ip = getIP()

                setting_dict = self.getSetting()
                count = int(setting_dict.get('registerMaxCount', 3))
                user.fundCount = count
                user.lastlandingtime = datetime.now().strftime('%Y/%m/%d %H:%M:%S')

                self.session.add(user)
                self.session.commit()
                return user.name, user.password, user.robotCode, user.fundCount, user.ip, user.allowLogin
        return name, None, self.get_mac_address(), 0, getIP(), False

    def checkRobotCode(self):
        robotCode = self.get_mac_address()
        user = self.session.query(UserInfo).filter_by(robotCode=robotCode).first()
        if user:
            if not user.allowLogin:
                return False
        return True

    def getSetting(self):
        settings = self.session.query(Setting).all()
        setting_dict = {}
        for s in settings:
            setting_dict[s.skey] = s.svalue
        return setting_dict

    def getNewVersion(self):
        vers = self.session.query(Update).order_by(Update.id).all()
        if vers and len(vers) > 0:
            ver = vers[-1]
            return ver.version, ver.url, ver.forcedUpdating
        return None, None, None

    def setSuggest(self, username, text):
        sug = Suggest()
        sug.username = username
        sug.text = text
        self.session.add(sug)
        self.session.commit()


    def __del__(self):
        close_all_sessions()



